package com.newcrm.db;

import com.newcrm.exception.DBException;
import com.newcrm.util.Util;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import org.apache.commons.dbutils.DbUtils;
import org.apache.commons.dbutils.GenKeyQueryRunner;
import org.apache.commons.dbutils.MyMapListHandler;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.ArrayHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import org.apache.log4j.Logger;

public class TagManager
{
    private static Logger logger = Logger.getLogger(TagManager.class.getName());

    public int add(int tagId, int customerId, int companyId, String userId)
    {
        if (tagId <= 0) {
            return 0;
        }

        Connection conn = ConnectionManager.getInstance().getConnection();
        if (conn == null) {
            return 0;
        }
        QueryRunner runner = new QueryRunner();
        try
        {
            return runner.update(conn, "INSERT INTO T_Customer_Tag_Info (CustomerId,TagId,CompanyId,CreateDate,CreateUserId) VALUES (?,?,?,NOW(),?)", new Object[] { Integer.valueOf(customerId),
                    Integer.valueOf(tagId), Integer.valueOf(companyId), userId });
        }
        catch (SQLException e) {
            logger.warn("add tag failed", e);
        } finally {
            try {
                DbUtils.close(conn);
            } catch (SQLException e) {
                logger.warn("close conn failed", e);
            }
        }
        return 0;
    }

    public int add(String tagName, int companyId, String userId)
            throws DBException
    {
        if ((tagName == null) || (tagName.trim().length() == 0)) {
            return 0;
        }

        Connection conn = ConnectionManager.getInstance().getConnection();
        if (conn == null) {
            throw new DBException();
        }

        GenKeyQueryRunner runner = new GenKeyQueryRunner(
                new ScalarHandler(), new String[] { "TagId" });
        try {
            Object[] result = (Object[])runner.query(conn, "SELECT TagId FROM T_Tag_Info WHERE CompanyId = ? AND TagName = ?",
                    new ArrayHandler(), new Object[] { Integer.valueOf(companyId), tagName });

            if (result != null) {
                return 0;
            }

            int num = runner.update(conn, "INSERT INTO T_Tag_Info (TagName,CompanyId,CreateDate,CreateUserId) VALUES (?,?,NOW(),?)", new Object[] { tagName,
                    Integer.valueOf(companyId), userId });
            if (num == 0) {
                return 0;
            }
            return ((Long)runner.getGeneratedKeys()).intValue();
        } catch (SQLException e) {
            logger.warn("add tag failed", e);
        } finally {
            try {
                DbUtils.close(conn);
            } catch (SQLException e) {
                logger.warn("close conn failed", e);
            }
        }
        return 0;
    }

    public List<String> addAll(int tagId, String customerIds, int companyId, String userId)
            throws DBException
    {
        if (tagId <= 0) {
            return null;
        }

        if ((customerIds == null) || (customerIds.trim().length() == 0)) {
            return null;
        }
        String[] ids = customerIds.split(",");
        if ((ids == null) || (ids.length == 0)) {
            return null;
        }
        Connection conn = ConnectionManager.getInstance().getConnection();
        if (conn == null) {
            throw new DBException();
        }

        List results = new ArrayList();
        QueryRunner runner = new QueryRunner();

        List params = new ArrayList();

        for (String id : ids)
            if ((id != null) && (id.trim().length() != 0))
            {
                int customerId = Util.str2Int(id);
                List info = new ArrayList();

                info.add(Integer.valueOf(customerId));
                info.add(Integer.valueOf(tagId));
                info.add(Integer.valueOf(companyId));
                info.add(userId);

                params.add(info.toArray(new Object[0]));
            }
        try
        {
            int[] result = runner.batch(conn, "INSERT IGNORE INTO T_Customer_Tag_Info (CustomerId,TagId,CompanyId,CreateDate,CreateUserId) VALUES (?,?,?,NOW(),?)",
                    (Object[][])params.toArray(new Object[0][]));
            if (result != null) {
                for (int i = 0; i < result.length; i++)
                    if (result[i] == 1)
                        results.add(String.valueOf(((Object[])params.get(i))[0]));
            }
        }
        catch (SQLException e)
        {
            logger.warn("add tag failed", e);
            try
            {
                DbUtils.close(conn);
            } catch (SQLException se) {
                logger.warn("close conn failed", se);
            }
        }
        finally
        {
            try
            {
                DbUtils.close(conn);
            } catch (SQLException e) {
                logger.warn("close conn failed", e);
            }
        }
        return results;
    }

    public int addAll(int tagId, List<Map<String, Object>> customerIds, int companyId, String userId)
            throws DBException
    {
        if (tagId <= 0) {
            return -1;
        }

        if ((customerIds == null) || (customerIds.size() == 0)) {
            return -1;
        }
        Connection conn = ConnectionManager.getInstance().getConnection();
        if (conn == null) {
            throw new DBException();
        }

        QueryRunner runner = new QueryRunner();

        List params = new ArrayList();

        for (Map id : customerIds) {
            int customerId = Util.str2Int(id.get("CustomerId"));
            if (customerId > 0)
            {
                List info = new ArrayList();

                info.add(Integer.valueOf(customerId));
                info.add(Integer.valueOf(tagId));
                info.add(Integer.valueOf(companyId));
                info.add(userId);

                params.add(info.toArray(new Object[0]));
            }
        }
        int num = 0;
        try {
            int[] result = runner.batch(conn, "INSERT IGNORE INTO T_Customer_Tag_Info (CustomerId,TagId,CompanyId,CreateDate,CreateUserId) VALUES (?,?,?,NOW(),?)",
                    (Object[][])params.toArray(new Object[0][]));
            if (result != null) {
                for (int i = 0; i < result.length; i++)
                    if (result[i] == 1)
                        num++;
            }
        }
        catch (SQLException e)
        {
            logger.warn("add tag failed", e);
            try
            {
                DbUtils.close(conn);
            } catch (SQLException se) {
                logger.warn("close conn failed", se);
            }
        }
        finally
        {
            try
            {
                DbUtils.close(conn);
            } catch (SQLException e) {
                logger.warn("close conn failed", e);
            }
        }
        return num;
    }

    public int update(int customerId, String tagIds, int companyId, String userId)
    {
        if (customerId <= 0) {
            return 0;
        }

        Connection conn = ConnectionManager.getInstance().getConnection();
        if (conn == null) {
            return 0;
        }

        QueryRunner runner = new QueryRunner();
        try
        {
            runner.update(conn, "DELETE FROM T_Customer_Tag_Info WHERE CustomerId = ?", Integer.valueOf(customerId));

            if ((tagIds == null) || (tagIds.trim().length() == 0)) {
                return 0;
            }
            String[] ids = tagIds.split(",");
            if ((ids == null) || (ids.length == 0)) {
                return 0;
            }

            List params = new ArrayList();
            for (String id : ids) {
                if ((id != null) && (id.trim().length() != 0))
                {
                    List info = new ArrayList();

                    info.add(Integer.valueOf(customerId));
                    info.add(id);
                    info.add(Integer.valueOf(companyId));
                    info.add(userId);

                    params.add(info.toArray(new Object[0]));
                }
            }
            int[] result = runner.batch(conn, "INSERT IGNORE INTO T_Customer_Tag_Info (CustomerId,TagId,CompanyId,CreateDate,CreateUserId) VALUES (?,?,?,NOW(),?)",
                    (Object[][])params.toArray(new Object[0][]));

            return result.length;
        }
        catch (SQLException e) {
            logger.warn("delete customer tag failed", e);
        } finally {
            try {
                DbUtils.close(conn);
            } catch (SQLException e) {
                logger.warn("close conn failed", e);
            }
        }
        return 0;
    }

    public int delete(int customerId, int tagId)
            throws DBException
    {
        if ((customerId <= 0) || (tagId <= 0)) {
            return 0;
        }
        Connection conn = ConnectionManager.getInstance().getConnection();
        if (conn == null) {
            throw new DBException();
        }
        QueryRunner runner = new QueryRunner();
        try
        {
            return runner.update(conn, "DELETE FROM T_Customer_Tag_Info WHERE CustomerId = ? AND TagId = ? ", new Object[] {
                    Integer.valueOf(customerId), Integer.valueOf(tagId) });
        } catch (SQLException e) {
            logger.warn("delete customer tag failed", e);
        } finally {
            try {
                DbUtils.close(conn);
            } catch (SQLException e) {
                logger.warn("close conn failed", e);
            }
        }

        return 0;
    }

    public int delete(int tagId)
            throws DBException
    {
        if (tagId <= 0) {
            return 0;
        }
        Connection conn = ConnectionManager.getInstance().getConnection();
        if (conn == null) {
            throw new DBException();
        }
        QueryRunner runner = new QueryRunner();
        try
        {
            int result = runner.update(conn, "DELETE FROM T_Tag_Info WHERE TagId = ?", Integer.valueOf(tagId));
            runner.update(conn, "DELETE FROM T_Customer_Tag_Info WHERE TagId = ?", Integer.valueOf(tagId));
            return result;
        } catch (SQLException e) {
            logger.warn("delete tag failed", e);
        } finally {
            try {
                DbUtils.close(conn);
            } catch (SQLException e) {
                logger.warn("close conn failed", e);
            }
        }
        return 0;
    }

    public List<Map<String, Object>> list(int companyId)
            throws DBException
    {
        Connection conn = ConnectionManager.getInstance().getConnection();
        if (conn == null) {
            return null;
        }

        QueryRunner runner = new QueryRunner();
        try
        {
            List result = (List)runner.query(conn,
                    "SELECT TagId, TagName FROM T_Tag_Info WHERE CompanyId = ? ORDER BY CONVERT(TagName USING GBK) ASC, CreateDate DESC", new MyMapListHandler(), new Object[] { Integer.valueOf(companyId) });
            return result;
        } catch (SQLException e) {
            throw new DBException();
        } finally {
            try {
                DbUtils.close(conn);
            } catch (SQLException e) {
                logger.warn("close conn failed", e);
            }
        }
    }

    public List<Map<String, Object>> getCustomersTags(String customerIds)
            throws DBException, SQLException
    {
        if ((customerIds == null) || (customerIds.trim().length() == 0)) {
            return null;
        }
        Connection conn = ConnectionManager.getInstance().getConnection();
        if (conn == null) {
            throw new DBException();
        }

        QueryRunner runner = new QueryRunner();
        try {
            StringBuffer sql = new StringBuffer("SELECT A.CustomerId, B.TagId, B.TagName FROM T_Customer_Tag_Info AS A INNER JOIN T_Tag_Info AS B ON A.TagId = B.TagId WHERE A.CustomerId IN ( ");
            sql.append(customerIds).append(") ORDER BY A.CustomerId");
            return (List)runner.query(conn, sql.toString(), new MyMapListHandler());
        } finally {
            try {
                DbUtils.close(conn);
            } catch (SQLException e) {
                logger.warn("close conn failed", e);
            }
        }
    }

    public List<Map<String, Object>> getCustomersTagIds(StringBuffer customerIds)
    {
        if ((customerIds == null) || (customerIds.length() == 0)) {
            return null;
        }
        Connection conn = ConnectionManager.getInstance().getConnection();
        if (conn == null) {
            return null;
        }

        QueryRunner runner = new QueryRunner();
        try {
            StringBuffer sql = new StringBuffer("SELECT CustomerId, TagId FROM T_Customer_Tag_Info WHERE CustomerId IN ( ");
            sql.append(customerIds).append(") ORDER BY CustomerId");
            return (List)runner.query(conn, sql.toString(), new MyMapListHandler());
        } catch (SQLException e) {
            logger.warn("get customer tags id failed", e);
        } finally {
            try {
                DbUtils.close(conn);
            } catch (SQLException e) {
                logger.warn("close conn failed", e);
            }
        }
        return null;
    }

    public int update(int tagId, String tagName, int companyId)
    {
        if (tagId < 0) {
            return 0;
        }

        if ((tagName == null) || (tagName.length() == 0)) {
            return 0;
        }

        Connection conn = ConnectionManager.getInstance().getConnection();
        if (conn == null) {
            return -1;
        }

        QueryRunner runner = new QueryRunner();
        try
        {
            Object[] result = (Object[])runner.query(conn, "SELECT TagId FROM T_Tag_Info WHERE CompanyId = ? AND TagName = ? AND TagId <> ?",
                    new ArrayHandler(), new Object[] { Integer.valueOf(companyId), tagName, Integer.valueOf(tagId) });
            if (result != null)
                return 0;
        }
        catch (SQLException e1) {
            logger.warn("check group name failed", e1);
            return 0;
//            try
//            {
//                return runner
//                        .update(conn, "UPDATE T_Tag_Info SET TagName = ? WHERE TagId = ?", new Object[] { tagName, Integer.valueOf(tagId) });
//            } catch (SQLException e) {
//                logger.warn("update tag failed", e);
//            }
        } finally {
            try {
                DbUtils.close(conn);
            } catch (SQLException e) {
                logger.warn("close conn failed", e);
            }
        }
        try
        {
            DbUtils.close(conn);
        } catch (SQLException e) {
            logger.warn("close conn failed", e);
        }

        return 0;
    }
}